/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import POJO.Invoice;
import POJO.Invoiceline;
import Util.DataAccessUtil;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 * @author Nguyễn Thi Mỷ Diện (1140214)
 */
public class InvoiceDAO {   
    public static List<Invoice> getListInvoices() throws Exception{
        String hql = "SELECT p from Invoice p "
                     +"LEFT JOIN FETCH p.store "
                     +"LEFT JOIN FETCH p.customer";       
        
        return DataAccessUtil.getList(hql);
    }
     
    public static List<Invoice> getListInvoices(int customerId) throws Exception{
        String hql = "SELECT p from Invoice p "
                     +"LEFT JOIN FETCH p.store "
                     +"LEFT JOIN FETCH p.customer"
                     +"WHERE p.customer.id = :customerId";    
        
        return DataAccessUtil.getList(hql);
    }
    
    public static List<Invoiceline> getListInvoicelines(String invoiceId) throws Exception{
        String hql = "SELECT d from Invoiceline d "
                      +"LEFT JOIN FETCH d.product p "
                      +"LEFT JOIN FETCH p.productcat c "
                      +"WHERE d.invoice.id = :invoiceId";       
        Map<String,Object> params = new HashMap<>();
        params.put("invoiceId", invoiceId);
        return DataAccessUtil.getList(hql,params);
    }
    
    public static boolean checkExistsInvoice(String id) throws Exception{
        String hql = "SELECT i FROM Invoice i WHERE i.id = :id";
        Map<String,Object> params = new HashMap<>();
        params.put("id", id);
        return DataAccessUtil.getList(hql,params).size() > 0;
    }
    public static List<Invoice> searchInvoices(Map<String,Object> whCdtions) throws Exception{
        String id = whCdtions.get("id").toString();
        String customerName = whCdtions.get("customerName").toString();
        Date dateFrom =  (Date) whCdtions.get("dateForm");
        Date dateTo =  (Date) whCdtions.get("dateTo");
        int productCatId =  (int) whCdtions.get("productCatId");
        String productId =  (String) whCdtions.get("productId");
        
        String hql =  "SELECT DISTINCT w FROM Invoice w "
                    +"LEFT JOIN FETCH w.customer e "
                    +"LEFT JOIN FETCH w.invoicelines d "
                    +"LEFT JOIN FETCH d.product p "
                    +"LEFT JOIN FETCH p.productcat c "  
                    +"WHERE 1 = 1 ";
        Map<String,Object> params = new HashMap<>();
        
        if(!id.isEmpty()){
            hql += "AND str(w.id) like :idWh ";
            params.put("idWh", "%"+id+"%");
        }
        if(!customerName.isEmpty()){
            hql += "AND lower(e.name) like :employeeName ";
            params.put("employeeName", "%"+customerName.toLowerCase()+"%");
        }
        if(productCatId > 0){
            hql += "AND c.id = :productCatId ";
            params.put("productCatId", productCatId);
            if(!productId.isEmpty()){
                hql += "AND p.id = :productId ";
                params.put("productId", productId);
            }
        }
        
        hql += "AND w.invoicedate >= :dateFrom AND w.invoicedate <= :dateTo ";
        params.put("dateFrom", dateFrom);
        params.put("dateTo", dateTo);  
        
        return DataAccessUtil.getList(hql, params);
    }  
}
